-- Table: team_allocation
-- DROP TABLE team_allocation;
CREATE TABLE team_allocation
(
  id serial NOT NULL,
  id_project integer,
  id_phase integer,
  id_employee integer,
  id_project_role integer,
  man_day integer,
  description character varying(255),
  remark character varying(255),
  status integer DEFAULT 0,
  created timestamp without time zone DEFAULT now(),
  creator integer,
  updated timestamp without time zone DEFAULT now(),
  updator integer,
  deleted boolean DEFAULT false,
  deletion_time timestamp without time zone,
  CONSTRAINT team_allocation_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE team_allocation OWNER TO postgres;

--procedures
CREATE OR REPLACE FUNCTION sp_team_allocation_insert(
	_id_project	integer DEFAULT null,
	_id_phase	integer DEFAULT null,
	_id_employee	integer DEFAULT null,
	_id_project_role	integer DEFAULT null,
	_man_day	integer DEFAULT null,
	_description	character varying(255) DEFAULT null,
	_remark	character varying(255) DEFAULT null,
	_status	integer DEFAULT null,
	_creator	integer DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO team_allocation(id_project,id_phase,id_employee,id_project_role,man_day,description,remark,status,creator,updator,deletion_time)
	VALUES(_id_project,_id_phase,_id_employee,_id_project_role,_man_day,_description,_remark,_status,_creator,_creator,null);
RETURN currval('team_allocation_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_team_allocation_select(_id	integer)
RETURNS SETOF team_allocation
AS $$
BEGIN
	RETURN QUERY SELECT * FROM team_allocation WHERE id = _id;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_team_allocation_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_id_project	integer DEFAULT null,
	_id_phase	integer DEFAULT null,
	_id_employee	integer DEFAULT null,
	_id_project_role	integer DEFAULT null,
	_man_day	integer DEFAULT null,
	_description	character varying(255) DEFAULT null,
	_remark	character varying(255) DEFAULT null,
	_status	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null
)
RETURNS SETOF team_allocation
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM team_allocation WHERE
	 	(_id is null OR id = _id)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_id_phase is null OR id_phase = _id_phase)	
	AND (_id_employee is null OR id_employee = _id_employee)	
	AND (_id_project_role is null OR id_project_role = _id_project_role)	
	AND (_man_day is null OR man_day = _man_day)	
	AND (_description is null OR description = _description)	
	AND (_remark is null OR remark = _remark)	
	AND (_status is null OR status = _status)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_team_allocation_count (
	_id	integer DEFAULT null,
	_id_project	integer DEFAULT null,
	_id_phase	integer DEFAULT null,
	_id_employee	integer DEFAULT null,
	_id_project_role	integer DEFAULT null,
	_man_day	integer DEFAULT null,
	_description	character varying(255) DEFAULT null,
	_remark	character varying(255) DEFAULT null,
	_status	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM team_allocation WHERE
	 	(_id is null OR id = _id)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_id_phase is null OR id_phase = _id_phase)	
	AND (_id_employee is null OR id_employee = _id_employee)	
	AND (_id_project_role is null OR id_project_role = _id_project_role)	
	AND (_man_day is null OR man_day = _man_day)	
	AND (_description is null OR description = _description)	
	AND (_remark is null OR remark = _remark)	
	AND (_status is null OR status = _status)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_team_allocation_delete(_id integer)
	RETURNS INT as
$$
BEGIN
		DELETE FROM team_allocation
 		WHERE "id" = _id;
		RETURN 0;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_team_allocation_update 
(	_id integer,
	_id_project integer,
	_id_phase integer,
	_id_employee integer,
	_id_project_role integer,
	_man_day integer,
	_description character varying(255),
	_remark character varying(255),
	_status integer,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE team_allocation 
	SET 		id_project = _id_project,
		id_phase = _id_phase,
		id_employee = _id_employee,
		id_project_role = _id_project_role,
		man_day = _man_day,
		description = _description,
		remark = _remark,
		status = _status,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
